Modul 6 von 15 · 📖 6 min Lesezeit · ⏱ 30 min gesamt
FI-DPA 06 SQL für Analytik
Inhaltsverzeichnis (5 Abschnitte)
FI-DPA 06 SQL für Analytik
In diesem Modul vertiefen Sie Ihr SQL-Wissen speziell für analytische Aufgaben. Sie erlernen die Anwendung von Window Functions für komplexe Berechnungen über Datensatzgruppen, Common Table Expressions (CTEs) zur Strukturierung komplexer Abfragen sowie Pivot/Unpivot-Operationen zur Datenrotation. Zudem erhalten Sie Einblicke in Performance-Optimierung und die Interpretation von EXPLAIN-Plänen.
Die Kenntnisse dieses Moduls sind essenziell für die effiziente Analyse großer Datenmengen und die Erstellung komplexer Berichte in Datenbankumgebungen.
Konzepte und Hintergrund
- Window Functions
- Funktionen, die auf einem Satz von Zeilen (dem "Fenster") arbeiten, ohne diese zu gruppieren. Sie ermöglichen Berechnungen wie gleitende Durchschnitte, Rangfolgen und kumulative Summen innerhalb definierter Partitionen.
- Common Table Expressions (CTEs)
- Temporäre, benannte Resultatmengen innerhalb einer SQL-Abfrage, die die Lesbarkeit und Modularität komplexer Abfragen verbessern. Ein CTE wird mit der WITH-Klausel definiert und existiert nur für die Dauer der Abfrage.
- Pivot/Unpivot
- Pivot transformiert Zeilen in Spalten, während Unpivot das Gegenteil tut. Diese Operationen sind nützlich, um Daten für Berichte oder Dashboards umzuformen, indem man Spaltenwerte in Zeilen und umgekehrt wandelt.
- EXPLAIN-Pläne
- Zeigen die Ausführungsstrategie einer SQL-Abfrage an. Sie enthalten Informationen über verwendete Indizes, Join-Methoden, geschätzte Kosten und die Reihenfolge der Operationen, was zur Performance-Optimierung unerlässlich ist.
Praktische Schritte
- Identifizieren Sie die analytische Anforderung und strukturieren Sie diese in logische Schritte. Dies bildet die Grundlage für die Wahl der richtigen SQL-Funktionen.
- Verwenden Sie CTEs, um komplexe Abfragen in lesbare Teile zu zerlegen. Beginnen Sie mit der Definition der CTEs vor der Hauptabfrage.
- Implementieren Sie Window Functions mit der OVER-Klausel, um Partitionen und Reihenfolgen für Berechnungen festzulegen.
- Wenden Sie Pivot/Unpivot an, um Daten für die gewünschte Darstellung umzuformen. Verwenden Sie CASE-Anweisungen innerhalb von Pivot-Operationen für komplexe Transformationen.
- Analysieren Sie EXPLAIN-Pläne mit EXPLAIN ANALYZE, um Engpässe zu identifizieren. Achten Sie auf hohe Kosten, vollständige Tabellenscans und ineffiziente Join-Strategien.
- Optimieren Sie Abfragen durch gezielte Verwendung von Indizes, das Reduzieren von Datenmengen in frühen Abfragephasen und das Vermeiden unnötiger Operationen.
- Testen Sie komplexe Abfragen mit repräsentativen Datensätzen, bevor Sie sie in der Produktionsumgebung ausführen.
Häufige Fallstricke
Weiterführende Ressourcen
- PostgreSQL Window Functions Tutorial
- Microsoft SQL Server Documentation
- SQL Shack: Pivot and Unpivot Operations
- Use The Index, Luke!
- EXPLAIN Depesz Visualizer
Wissens-Check
Vier Fragen zur Selbstkontrolle. Klicken Sie jede Frage an, um die richtige Antwort und Erklärung zu sehen.
Was ist der Hauptunterschied zwischen Window Functions und regulären Aggregatfunktionen in SQL?
- A) Window Functions können nur auf numerische Daten angewendet werden
- B) Window Functions gruppieren Zeilen nicht, sondern führen Berechnungen auf einem Fenster von Zeilen durch
- C) Window Functions erfordern immer eine GROUP BY-Klausel
- D) Window Functions können nur mit der DISTINCT-Klausel verwendet werden
Richtige Antwort: B. Window Functions arbeiten auf einem Fenster von Zeilen, ohne diese zu gruppieren, während reguläre Aggregatfunktionen Zeilen gruppieren und pro Gruppe einen Wert zurückgeben. Option A ist falsch, da Window Functions auf verschiedenen Datentypen arbeiten. Option C ist falsch, da Window Functions ohne GROUP BY funktionieren. Option D ist falsch, da DISTINCT nicht mit Window Functions verwendet wird.
Welches ist der Hauptvorteil der Verwendung von Common Table Expressions (CTEs) in komplexen SQL-Abfragen?
- A) CTEs verbessern die Performance von Abfragen immer
- B) CTEs ermöglichen rekursive Abfragen
- C) CTEs erhöhen die Lesbarkeit und Modularität von Abfragen
- D) CTEs können nur mit SELECT-Anweisungen verwendet werden
Richtige Antwort: C. CTEs verbessern die Lesbarkeit und Modularität, indem sie komplexe Abfragen in logische, benannte Teile zerlegen. Option A ist falsch, da CTEs nicht immer die Performance verbessern. Option B ist teilweise richtig, aber nicht der Hauptvorteil. Option D ist falsch, da CTEs mit INSERT, UPDATE, DELETE etc. verwendet werden können.
Was ist der Hauptzweil von Pivot/Unpivot-Operationen in SQL?
- A) Daten zu komprimieren, um Speicherplatz zu sparen
- B) Daten zwischen verschiedenen Tabellen zu verschieben
- C) Datenstruktur zu ändern, indem Zeilen in Spalten und umgekehrt transformiert werden
- D) Daten zu verschlüsseln, um Sicherheit zu erhöhen
Richtige Antwort: C. Pivot/Unpivot-Operationen ändern die Datenstruktur, indem sie Zeilen in Spalten (Pivot) oder Spalten in Zeilen (Unpivot) transformieren, oft für Berichte oder Dashboards. Option A ist falsch, da es nicht primär um Kompression geht. Option B ist falsch, da es nicht um das Verschieben von Daten geht. Option D ist falsch, da es keine Verschlüsselung ist.
Was können Sie aus einem EXPLAIN-Plan einer SQL-Abfrage nicht direkt ableiten?
- A) Die geschätzten Kosten der Abfrage
- B) Die genauen Datensätze, die von der Abfrage zurückgegeben werden
- C) Die verwendete Join-Strategie
- D) Die Reihenfolge der Operationen
Richtige Antwort: B. Ein EXPLAIN-Plan zeigt die Ausführungsstrategie, Kosten, Join-Methoden und Reihenfolge der Operationen, aber nicht die tatsächlichen Datensätze, die zurückgegeben werden. Option A ist falsch, da Kosten im EXPLAIN-Plan enthalten sind. Option C ist falsch, da Join-Strategien im EXPLAIN-Plan enthalten sind. Option D ist falsch, da die Reihenfolge der Operationen im EXPLAIN-Plan enthalten ist.